using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Windows.Forms;
using GrapeCity.ActiveAnalysis.DataSources;
using GrapeCity.ActiveAnalysis.Layout;
using GrapeCity.ActiveAnalysis.Samples;
using GrapeCity.ActiveAnalysis.Schema;
using template_dll.AssemblyResources;
using GrapeCity.ActiveAnalysis.Windows.Forms;

namespace template_dll.Dialogs
{
	/// <summary>
	/// Prompts the user to connect to a relational database.
	/// </summary>
	public sealed partial class RelationalConnectionDialog : Form
	{
		private IDataSource _dataSource;
		private ConnectionType _oldConnectionType = ConnectionType.None;
		private DataLinkDialog _dataLinkDialog;

		private bool _resetConnectionString;
		private bool _resetQuery;
		private const string DefaultQuery = "SELECT * FROM ...";
			
		/// <summary>
		/// Initializes an instance of the <see cref="RelationalConnectionDialog"/> class.
		/// </summary>
		public RelationalConnectionDialog()
		{
			InitializeComponent();

			_okButton.Enabled = false;

			foreach(ConnectionType connectionType in Enum.GetValues(typeof(ConnectionType)))
			{
				if (connectionType != ConnectionType.None && connectionType != ConnectionType.ADO)
					_dataSourceTypeCombo.Items.Add(connectionType);
			}
			_dataSourceTypeCombo.SelectedIndex = 0;

			_schemaFileTextBox.Enabled = false;
			_selectSchemaFileButton.Enabled = false;
			_autoGenerateSchemaCheckBox.Checked = true;

			_queryStringTextBox.Text = DefaultQuery;

			HandleCreated += delegate
				{
					_dataLinkDialog = new DataLinkDialog(this);
				};
		}

		/// <summary>
		/// Gets the connection string used to establish a connection to a data source. 
		/// </summary>
		public string ConnectionString
		{
			get { return _connectionStringTextBox.Text; }
		}

		/// <summary>
		/// Gets the query (commonly SQL statement) used to retrieve facts from a data source.
		/// </summary>
		public string QueryString
		{
			get { return _queryStringTextBox.Text; }
		}

		/// <summary>
		/// Gets the file that contains a schema of a cube.
		/// </summary>
		public string SchemaFile
		{
			get
			{
				if (_autoGenerateSchemaCheckBox.Checked)
					return AutoGeneratedSchemaFile;

				return _schemaFileTextBox.Text;
			}
		}

		/// <summary>
		/// Gets the name of user defined data source.
		/// This name is used to save data source properties in the local repository.
		/// </summary>
		public string DataSourceName
		{
			get { return _dataSourceNameTextBox.Text; }
		}

		/// <summary>
		/// Gets type of the data provider.
		/// </summary>
		public ConnectionType ConnectionType
		{
			get { return (ConnectionType)_dataSourceTypeCombo.SelectedItem; }
		}

		/// <summary>
		/// Gets data source instance that was used to verify connection properties.
		/// </summary>
		/// <remarks>
		/// When user pres' Test Connection button DataSource is created and simple query is executed. If user press OK button after the successfull connection then you should take care about this data source.
		/// </remarks>
		public IDataSource DataSource
		{
			get { return _dataSource; }
		}

		private string AutoGeneratedSchemaFile
		{
			get
			{
				string connectionString = _connectionStringTextBox.Text;
				
				IDbConnection connection;
                switch (ConnectionType)
				{
					case ConnectionType.Odbc:
						connection = new OdbcConnection(connectionString);
						break;
					case ConnectionType.OleDb:
						connection = new OleDbConnection(connectionString);
						break;
					case ConnectionType.Sql:
						connection = new SqlConnection(connectionString);
						break;
					default:
						return string.Empty;
				}

				SchemaBuilder builder;

				try
				{
					builder = SchemaBuilder.AutoGenerate(_dataSourceNameTextBox.Text + "Schema", connection, _queryStringTextBox.Text);
				}
				finally
				{
					connection.Close();
				}

				using (SaveFileDialog fileDialog = new SaveFileDialog())
				{
					fileDialog.Title = "Save schema file";
					fileDialog.Filter = Resources.GetString(StringsKey.SaveSchemaFilesFilter);
					fileDialog.RestoreDirectory = true;
					fileDialog.ValidateNames = true;
					if (fileDialog.ShowDialog(this) != DialogResult.OK)
					{
						throw new Exception("Connection to database cannot work without schema file.");
					}

					FileInfo fileInfo = new FileInfo(fileDialog.FileName);
					builder.SaveSchema(fileInfo);

					return fileInfo.FullName;
				}
			}
		}

		private void OnSelectSchemaFileButtonClick(object sender, EventArgs e)
		{
			using (OpenFileDialog fileDialog = new OpenFileDialog())
			{
				fileDialog.Multiselect = false;
				fileDialog.Title = "Open schema file";
				fileDialog.Filter = Resources.GetString(StringsKey.SaveSchemaFilesFilter);
				fileDialog.FilterIndex = 0;
				fileDialog.RestoreDirectory = true;
				fileDialog.CheckFileExists = true;
				if (!string.IsNullOrEmpty(SchemaFile))
					fileDialog.InitialDirectory = Path.GetDirectoryName(SchemaFile);
				if (fileDialog.ShowDialog(this) == DialogResult.OK)
				{
					_schemaFileTextBox.Text = fileDialog.FileName;
				}
			}
		}
        
		private void OnAutoGenerateSchema(object sender, EventArgs e)
		{
			_schemaFileTextBox.Enabled =
				_selectSchemaFileButton.Enabled = !_autoGenerateSchemaCheckBox.Checked;
		}

		private void OnTestConnectionButtonClick(object sender, EventArgs e)
		{
			bool exceptionHelperPassThrough = GrapeCity.ActiveAnalysis.ExceptionHelper.PassThrough;

			RdDataSource dataSource = new RdDataSource();
			try
			{
				GrapeCity.ActiveAnalysis.ExceptionHelper.PassThrough = true;

				dataSource.ConnectionType = ConnectionType;
				dataSource.ConnectionString = ConnectionString;
				dataSource.QueryString = QueryString;

				if (dataSource.ConnectionType == ConnectionType.None)
					throw new Exception("Wrong connection type.");
				if (string.IsNullOrEmpty(dataSource.ConnectionString))
					throw new Exception("Empty connection string.");
				if (string.IsNullOrEmpty(dataSource.QueryString))
					throw new Exception("Empty query string.");

				dataSource.CustomSchemaFile = SchemaFile;
				if (!File.Exists(dataSource.CustomSchemaFile))
					throw new Exception("Missed schema file.");
				
				dataSource.Connect();

				if (dataSource.Schema.Fields.Count == 0)
					throw new Exception("Schema should contain at least one field.");

				SamplesImpl.ShowMessageBox("Connection was successfully established.", MessageBoxIcon.Information, MessageBoxButtons.OK, this);

				_dataSource = dataSource;
				UpdateOkButton();
			}
			catch (Exception ex)
			{
				SamplesImpl.ShowMessageBox(ex, MessageBoxIcon.Error, MessageBoxButtons.OK, this);
			}
			finally
			{
				GrapeCity.ActiveAnalysis.ExceptionHelper.PassThrough = exceptionHelperPassThrough;
			}
		}

		private void OnConnectionProperties(object sender, EventArgs e)
		{
			string connectionString = null;
			
			switch (ConnectionType)
			{
#if HAS_CONNECTION_UI
				case ConnectionType.Odbc:
					// see http://code.msdn.microsoft.com/Connection
					Microsoft.Data.ConnectionUI.DataConnectionDialog odbcDialog = new Microsoft.Data.ConnectionUI.DataConnectionDialog();
					odbcDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.OdbcDataSource);
					odbcDialog.UnspecifiedDataSource.Providers.Add(Microsoft.Data.ConnectionUI.DataProvider.OdbcDataProvider);
					if (Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(odbcDialog, this) != DialogResult.OK)
					{
						odbcDialog.Dispose();
						return;
					}
					connectionString = odbcDialog.ConnectionString;
					odbcDialog.Dispose();
					break;
				case ConnectionType.Sql:
					// see http://code.msdn.microsoft.com/Connection
					Microsoft.Data.ConnectionUI.DataConnectionDialog sqlDialog = new Microsoft.Data.ConnectionUI.DataConnectionDialog();
					sqlDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.SqlDataSource);
					sqlDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.SqlFileDataSource);
					sqlDialog.UnspecifiedDataSource.Providers.Add(Microsoft.Data.ConnectionUI.DataProvider.SqlDataProvider);
					if (Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(sqlDialog, this) != DialogResult.OK)
					{
						sqlDialog.Dispose();
						return;
					}
					connectionString = sqlDialog.ConnectionString;
					Microsoft.Data.ConnectionUI.DataProvider selectedProvider = sqlDialog.SelectedDataProvider;
					if (selectedProvider == Microsoft.Data.ConnectionUI.DataProvider.OleDBDataProvider)
						_dataSourceTypeCombo.SelectedItem = ConnectionType.OleDb;
					sqlDialog.Dispose();
					break;
#endif
				case ConnectionType.OleDb:
					if (_dataLinkDialog.IsDialogAvailable())
						connectionString = _dataLinkDialog.ShowDialog();
					else
					{
#if HAS_CONNECTION_UI
						// see http://code.msdn.microsoft.com/Connection
						Microsoft.Data.ConnectionUI.DataConnectionDialog oledbDialog = new Microsoft.Data.ConnectionUI.DataConnectionDialog();
						oledbDialog.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.AccessDataSource);
						oledbDialog.UnspecifiedDataSource.Providers.Add(Microsoft.Data.ConnectionUI.DataProvider.OleDBDataProvider);
						if (Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(oledbDialog, this) != DialogResult.OK)
						{
							oledbDialog.Dispose();
							return;
						}
						connectionString = oledbDialog.ConnectionString;
						oledbDialog.Dispose();
#else
						SamplesImpl.ShowMessageBox("Data connection dialog is anavailable.", MessageBoxIcon.Information, MessageBoxButtons.OK, this);
						return;
#endif
					}
					break;
				default:
					SamplesImpl.ShowMessageBox("Data connection dialog is anavailable.", MessageBoxIcon.Information, MessageBoxButtons.OK, this);
					return;
			}

			if (string.IsNullOrEmpty(connectionString))
				return;

			if (ConnectionType == ConnectionType.OleDb)
				GenerateSelectStatement(connectionString);
		
			_connectionStringTextBox.Text = connectionString;
			_resetConnectionString = true;
		}

		private void OnConnectionTypeChanged(object sender, EventArgs e)
		{
			_propertiesButton.Enabled = 
				ConnectionType == ConnectionType.OleDb
#if HAS_CONNECTION_UI
				|| ConnectionType == ConnectionType.Odbc || ConnectionType == ConnectionType.Sql
#endif
				;

			if (_resetQuery)
				_queryStringTextBox.Text = DefaultQuery;
			_resetQuery = false;

			if (_resetConnectionString ||
				string.IsNullOrEmpty(_connectionStringTextBox.Text) ||
				GetConnectionStringTemplate(_oldConnectionType) == _connectionStringTextBox.Text)
			{
				_oldConnectionType = ConnectionType;
				_connectionStringTextBox.Text = GetConnectionStringTemplate(ConnectionType);
			}
			_resetConnectionString = false;

			ReleaseDataSource();
			UpdateOkButton();
		}

		private void OnParameterChanged(object sender, EventArgs e)
		{
			if (string.IsNullOrEmpty(_connectionStringTextBox.Text) ||
				GetConnectionStringTemplate(_oldConnectionType) == _connectionStringTextBox.Text)
			{
				_oldConnectionType = ConnectionType;
				_connectionStringTextBox.Text = GetConnectionStringTemplate(ConnectionType);
			}
			
			ReleaseDataSource();
			UpdateOkButton();
		}

		private void OnConnectionTextChanged(object sender, EventArgs e)
		{
			ReleaseDataSource();
			UpdateOkButton();
		}
		
		private void GenerateSelectStatement(string connectionString)
		{
			try
			{
				OleDbConnection connection = new OleDbConnection(connectionString);
				connection.Open();
				try
				{
					DataTable schemaTable = connection.GetOleDbSchemaTable(
						OleDbSchemaGuid.Tables,
						new object[] {null, null, null, "TABLE"});
					StringBuilder query = new StringBuilder();
					query.Append("SELECT * FROM ");
					for (int i = 0; i < schemaTable.Rows.Count; i++)
					{
						string tableName = schemaTable.Rows[i][2].ToString();
						query.Append("[");
						query.Append(tableName);
						query.Append("] AS t");
						query.Append(i);
						if (i < schemaTable.Rows.Count - 1)
							query.Append(", ");
					}
					_queryStringTextBox.Text = query.ToString();
					_resetQuery = true;
				}
				finally
				{
					connection.Close();
				}
			}
			catch (Exception ex)
			{
				SamplesImpl.ShowMessageBox(ex, MessageBoxIcon.Error, MessageBoxButtons.OK, this);
			}
		}

		private static string GetConnectionStringTemplate(ConnectionType connectionType)
		{
			switch (connectionType)
			{
				case ConnectionType.OleDb:
					return "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=[file name];";
				case ConnectionType.Sql:
					return "Data Source=(local);Integrated Security=SSPI;Initial Catalog=[data base name];";
				case ConnectionType.Odbc:
					return "Driver={SQL Server};Server=(local);Trusted_Connection=Yes;Database=[data base name];";
			}
			return string.Empty;
		}

		private void ReleaseDataSource()
		{
			if (_dataSource != null)
			{
				_dataSource.Dispose();
				_dataSource = null;
			}
		}

		private void OnDataSourceNameTextBoxTextChanged(object sender, EventArgs e)
		{
			UpdateOkButton();
		}

		private void UpdateOkButton()
		{
			_okButton.Enabled = _dataSource != null && !string.IsNullOrEmpty(DataSourceName);
		}

		/// <summary>
		/// Release all resources.
		/// </summary>
		/// <param name="e"></param>
		protected override void OnClosed(EventArgs e)
		{
			base.OnClosed(e);

			if(DialogResult != DialogResult.OK)
				ReleaseDataSource();

			Debug.Assert(DialogResult != DialogResult.OK
				|| (DataSource != null && !string.IsNullOrEmpty(DataSourceName)));
		}
	}
}
